package com.vogue.supplymall.order.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.OrderDetailInfo;

/**
 * The type Order detail sql provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-02-07
 */
public class OrderDetailSqlProvider extends BaseSqlProvider {

    /**
     * Find order detail string.
     *
     * @param detailInfo the detail info
     * @return the string
     */
    public String findOrderDetail(OrderDetailInfo detailInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" o.ordernumber, ")
                .append(" o.totalprice, ")
                .append(" o.totalquantity, ")
                .append(" o.freight, ")
                .append(" o.memo, ")
                .append(" o.isInvoice, ")
                .append(" o.createdate, ")
                .append(" o.modifydate ordermodifydate, ")
                .append(" o.finalprice, ")
                .append(" oi.id oiid, ")
                .append(" oi.itemprice, ")
                .append(" oi.itemquantity, ")
                .append(" oi.storageid, ")
                .append(" oi.`storage`, ")
                // 2018/8/3
                .append(" oi.sendcount, ")
                .append(" oi.sendmoney, ")
                .append(" oi.diffcount, ")
                .append(" oi.diffmoney, ")
                .append(" oip.id oipid, ")
                .append(" oip.pname, ")
                .append(" oip.price, ")
                .append(" oip.productid, ")
                .append(" oip.attrplanid, ")
                .append(" oip.punit, ")
                .append(" oip.imgurl, ")
                .append(" oip.weight, ")
                /*卖家电话*/
                .append(" os.stel os_stel, ")
                .append(" os.shopid os_shopid, ")
                .append(" os.entname os_entname, ")
                .append(" os.shopname os_shopname, ")
                .append(" os.mcid os_mcid, ")
                // 20180905 未预付款查询添加
                .append(" os.syscodeid sellsyscodeid, ")
                .append(" os.organid sellorganid, ")
                /*订单支付信息*/
                // 2018/11/19 因为payment为多条，在后边另行查询
//                .append(" op.id op_id, ")
//                .append(" op.amount op_amount, ")
//                .append(" op.paymentnumber op_paymentnumber, ")
//                .append(" op.paymenttype op_paymenttype, ")
//                .append(" op.archivestatus archivestatus, ")
                .append(" ob.btel ob_btel, ")
                .append(" ob.buyeraddress ob_address, ")
                .append(" ob.buyername ob_name, ")
                .append(" ob.buyertype ob_buyertype, ")
                .append(" ob.entname ob_entname, ")
                .append(" ob.storename ob_storename,")
//                .append(" ob.addressinfo ob_addressinfo,")
                /*物流信息*/
                .append(" ffm.id ffm_id,")
                .append(" ffm.`status` ffm_status,")
                .append(" ffm.`type` ffm_type,")
                .append(" ifnull(ffm.senddate,'') ffm_senddate,")
                .append(" ifnull(ffm.arrivedate,'') ffm_arrivedate,")
                .append(" ffm.number ffm_number,")
                .append(" ffm.companycode ffm_companycode,")
                .append(" oss.id statusid, ")
                // 2018/2/26 add供应链侧是否拉取过订单
                .append(" oss.pullstate, ")
                // 2018/7/2 添加买家拉取状态
                .append(" oss.buypullstate buypullstate, ")
                .append(" oss.orderstatus, ")
                .append(" oss.iscancel, ")
                .append(" oss.modifydate ");
        sql.append(" FROM ")
                .append(" `order` o ")
                .append(" INNER JOIN order_status oss ON (o.id = oss.orderid) ")
                .append(" INNER JOIN order_buyer ob ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_seller os ON (o.id = os.orderid) ")
                .append(" INNER JOIN order_item oi ON (o.id = oi.orderid) ")
                .append(" INNER JOIN order_item_product oip ON ( ")
                .append(" o.id = oip.orderid ")
                .append(" AND oi.id = oip.orderitemid) ")
                .append(" LEFT JOIN fulfillment ffm on (ffm.orderid = o.id) ");
//                .append(" LEFT JOIN (select GROUP_CONCAT(id) id,GROUP_CONCAT(amount) amount,GROUP_CONCAT(paymentnumber) paymentnumber,GROUP_CONCAT(paymenttype) paymenttype,GROUP_CONCAT(archivestatus) archivestatus,GROUP_CONCAT(paymentmode) paymentmode,orderid from order_payment group by orderid) op on (o.id = op.orderid) ");
        sql.append(" WHERE ")
                .append(" o.id = #{orderid} ")
                .append(" AND o.disabledflag = 0 ")
                .append(" ORDER BY ")
                .append(" oi.createdate ");
        return DealStatus.formatShadeSql(sql.toString());
    }
}
